package com.gbase8c.dmt.db.oracle;

import com.gbase8c.dmt.db.object.RoleObject;
import com.gbase8c.dmt.db.object.UserObject;
import com.gbase8c.dmt.model.migration.dto.DataSourceDto;
import com.gbase8c.dmt.model.migration.dto.RoleDto;
import com.gbase8c.dmt.model.migration.dto.UserDto;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.ColumnListHandler;

import java.util.List;
import java.util.Map;
import java.util.Set;

@Slf4j
public class UserObjectImpl extends MetaImpl implements UserObject {

    protected RoleObject roleObject;

    public UserObjectImpl(DataSourceDto dataSourceDto) {
        super(dataSourceDto);
        this.roleObject = new RoleObjectImpl(dataSourceDto);
    }

    @Override
    public List<String> getNames(Map<String, Object> params) {
        String sql = "select username from dba_users where TO_NUMBER(TO_CHAR(created, 'yyyymmdd')) not in (select min(TO_NUMBER(TO_CHAR(created, 'yyyymmdd'))) from dba_users)";
        List<String> userNames = query(sql, new ColumnListHandler<String>());
        return userNames;
    }

    @Override
    public UserDto get(String name, Map<String, Object> params) {
        //SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = ? and table_name = ?
        String privsql = "SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = ?";

        String databaseNameTsql = "SELECT DISTINCT TABLE_NAME from dba_tab_privs WHERE grantee = ? AND TABLE_NAME = GRANTOR";
        String databasePrivGrantorsql = "SELECT DISTINCT GRANTOR from dba_tab_privs WHERE grantee = ? AND TABLE_NAME = ?";

        String schemaNameTsql = "SELECT DISTINCT OWNER from dba_tab_privs WHERE grantee = ? AND TABLE_NAME <> GRANTOR";
        String tableNameTsql = "SELECT DISTINCT TABLE_NAME from dba_tab_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME <> GRANTOR";
        String tablePrivGrantorsql = "SELECT DISTINCT GRANTOR from dba_tab_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ?";

        String schemaNameCsql = "SELECT DISTINCT OWNER from dba_col_privs WHERE grantee = ?";
        String tableNameCsql = "SELECT DISTINCT TABLE_NAME from dba_col_privs WHERE grantee = ? AND OWNER = ?";
        String columnNameCsql = "SELECT DISTINCT COLUMN_NAME from dba_col_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ?";
        String columnPrivGrantorsql = "SELECT DISTINCT GRANTOR from dba_col_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME  = ?";

        String databasePrivsql = "SELECT PRIVILEGE from dba_tab_privs WHERE grantee = ? AND TABLE_NAME = ?";
        String tablePrivssql = "SELECT PRIVILEGE from dba_tab_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ?";
        //String tablePrivsql = "select TABLE_NAME, Table_priv AS privileges from mysql.tables_priv where user = ?";
        String columnPrivsql = "SELECT PRIVILEGE from dba_col_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME =? AND COLUMN_NAME=?";

        String grantedRoleNamessql = "SELECT GRANTED_ROLE from dba_role_privs WHERE GRANTEE = ?";

        String allRoleNamessql =
                "select role from dba_roles where role not in ('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE','IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER'," +
                        "'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','OEM_MONITOR','HS_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','JAVA_ADMIN','JAVA_DEPLOY','CTXAPP','GATHER_SYSTEM_STATISTICS','LOGSTDBY_ADMINISTRATOR'," +
                        "'GLOBAL_AQ_USER_ROLE','WM_ADMIN_ROLE','EJBCLIENT','XDBADMIN','AUTHENTICATEDUSER','OLAP_DBA','OEM_ADVISOR','SCHEDULER_ADMIN','XDBWEBSERVICES','OLAP_USER','MGMT_USER','DBFS_ROLE','DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE'," +
                        "'ADM_PARALLEL_EXECUTE_TASK','HS_ADMIN_SELECT_ROLE','HS_ADMIN_EXECUTE_ROLE','JMXSERVER','XDB_SET_INVOKER','XDB_WEBSERVICES_WITH_PUBLIC','XDB_WEBSERVICES_OVER_HTTP','ORDADMIN','OLAP_XS_ADMIN','SPATIAL_CSW_ADMIN','CSW_USR_ROLE','CWM_USER'," +
                        "'SPATIAL_WFS_ADMIN','WFS_USR_ROLE','OLAPI_TRACE_USER','OWB_USER','APEX_ADMINISTRATOR_ROLE','OWB$CLIENT','OWB_DESIGNCENTER_VIEW','PDB_DBA','AUDIT_ADMIN','AUDIT_VIEWER','CAPTURE_ADMIN','CDB_DBA','APPLICATION_TRACE_VIEWER','GSMUSER_ROLE'," +
                        "'PROVISIONER','XS_SESSION_ADMIN','XS_NAMESPACE_ADMIN','XS_CACHE_ADMIN','XS_CONNECT','GSM_POOLADMIN_ROLE','OPTIMIZER_PROCESSING_RATE','DBMS_MDX_INTERNAL','RECOVERY_CATALOG_OWNER_VPD','RECOVERY_CATALOG_USER','EM_EXPRESS_BASIC','EM_EXPRESS_ALL'," +
                        "'SYSUMF_ROLE','GSMADMIN_ROLE','GDS_CATALOG_SELECT','GGSYS_ROLE','GGSYS_ROLE','SODA_APP','DATAPATCH_ROLE','DBJAVASCRIPT','RDFCTX_ADMIN','LBAC_DBA','DV_SECANALYST','DV_REALM_OWNER','DV_MONITOR','DV_ADMIN','DV_OWNER','DV_ACCTMGR','DV_PUBLIC'," +
                        "'DV_PATCH_ADMIN','DV_STREAMS_ADMIN','DV_GOLDENGATE_ADMIN','DV_XSTREAM_ADMIN','DV_GOLDENGATE_REDO_ACCESS','DV_AUDIT_CLEANUP','DV_DATAPUMP_NETWORK_LINK','DV_POLICY_OWNER','DV_REALM_RESOURCE','XDB_WEBSERVICES','TIMESERIES_DEVELOPER','TIMESERIES_DBA'," +
                        "'SNMPAGENT','SALES_HISTORY_ROLE','WKUSER')";

//        //SELECT COLUMN_NAME from information_schema.`COLUMNS` where table_name='view_students_class';
//        String columnSql = "SELECT table_schema as `schema`, column_name , data_type " +
//                "FROM information_schema.columns WHERE table_schema = ? AND table_name = ?";
        UserDto userDto = UserDto.builder().build();
        UserDto.SysPrivDto sysPrivDto = new UserDto.SysPrivDto();
        List<UserDto.DatabasePrivDto> databasePrivDtos = Lists.newArrayList();
        List<UserDto.TablePrivDto> tablePrivDtos = Lists.newArrayList();
        List<UserDto.ColumnPrivDto> columnPrivDtos = Lists.newArrayList();
        List<RoleDto> roleDtos = Lists.newArrayList();
        List<Set<String>> allNames = Lists.newArrayList();
        Set<String> allSchemaNames = Sets.newHashSet();
        Set<String> allTableNames = Sets.newHashSet();
        Set<String> allColumnNames = Sets.newHashSet();

            BeanProcessor beanProcessor = new GenerousBeanProcessor();
            RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
            List<String> sysPriv = null;
            sysPriv = query(privsql, new ColumnListHandler<String>(), name);

            //系统权限赋值
            sysPrivilege2String(sysPrivDto,null,sysPriv);

            //库级权限单独处理，如果表名和grantor名相同，则认为是库级权限，将schemaName设置为tableName
            List<String> databaseNamesT = query(databaseNameTsql, new ColumnListHandler<String>(), name);
            if (!databaseNamesT.isEmpty() && databaseNamesT != null){
                for (String databaseName:databaseNamesT){
                    allSchemaNames.add(databaseName);
                    UserDto.DatabasePrivDto databasePrivDto = new UserDto.DatabasePrivDto();
                    List<String> databasePrivs = query(databasePrivsql, new ColumnListHandler<String>(), name,databaseName);
                    List<String> databaseGrantor = query(databasePrivGrantorsql, new ColumnListHandler<String>(), name,databaseName);
                    databasePrivDto.setSchemaName(databaseName);
                    databasePrivDto.setGrantor(databaseGrantor.get(0));
                    databasePrivDto.setGrantee(name);
                    //库级权限赋值
                    databasePrivilege2String(databasePrivDto,null,databasePrivs);
                    databasePrivDtos.add(databasePrivDto);
                }
            }

            //表级权限，查询表名与grantor不同名的，正常查询即可
            List<String> schemaNamesT = query(schemaNameTsql, new ColumnListHandler<String>(), name);
            if (!schemaNamesT.isEmpty() && schemaNamesT != null){
                for (String schemaName:schemaNamesT){
                    allSchemaNames.add(schemaName);
                    List<String> tabaleNames = query(tableNameTsql, new ColumnListHandler<String>(), name,schemaName);
                    for (String tableName:tabaleNames){
                        allTableNames.add(tableName);
                        UserDto.TablePrivDto tablePrivDto = new UserDto.TablePrivDto();
                        List<String> tablePrivs = query(tablePrivssql, new ColumnListHandler<String>(), name,schemaName,tableName);
                        List<String> tableGrantor = query(tablePrivGrantorsql, new ColumnListHandler<String>(), name,schemaName,tableName);
                        tablePrivDto.setSchemaName(schemaName);
                        tablePrivDto.setTableName(tableName);
                        tablePrivDto.setGrantor(tableGrantor.get(0));
                        tablePrivDto.setGrantee(name);
                        //表级权限赋值
                        tablePrivilege2String(tablePrivDto,null,tablePrivs);
                        tablePrivDtos.add(tablePrivDto);
                    }
                }
            }

            //列级权限
            List<String> schemaNamesC = query(schemaNameCsql, new ColumnListHandler<String>(), name);
            if (!schemaNamesC.isEmpty() && schemaNamesC !=null){
                for (String schemaName:schemaNamesC){
                    allSchemaNames.add(schemaName);
                    List<String> tabaleNames = query(tableNameCsql, new ColumnListHandler<String>(), name,schemaName);
                    for (String tableName:tabaleNames){
                        allTableNames.add(tableName);
                        List<String> columnNames = query(columnNameCsql, new ColumnListHandler<String>(), name,schemaName,tableName);
                        for (String columnName:columnNames){
                            allColumnNames.add(columnName);
                            UserDto.ColumnPrivDto columnPrivDto = new UserDto.ColumnPrivDto();
                            List<String> columnPrivGrantor = query(columnPrivGrantorsql, new ColumnListHandler<String>(), name,schemaName,tableName,columnName);
                            List<String> columnPrivs = query(columnPrivsql, new ColumnListHandler<String>(), name,schemaName,tableName,columnName);
                            columnPrivDto.setSchemaName(schemaName);
                            columnPrivDto.setTableName(tableName);
                            columnPrivDto.setColumnName(columnName);
                            columnPrivDto.setGrantor(columnPrivGrantor.get(0));
                            columnPrivDto.setGrantee(name);
                            //列级权限赋值
                            columnPrivilege2String(columnPrivDto,null,columnPrivs);
                            columnPrivDtos.add(columnPrivDto);
                        }
                    }
                }
            }

            //查询用户被赋予的角色，Oracle有专门的语句
            List<String> grantedRoleNames = query(grantedRoleNamessql, new ColumnListHandler<String>(), name);
            List<String> allRoleNames = query(allRoleNamessql,new ColumnListHandler<String>());

            if (!grantedRoleNames.isEmpty() && !allRoleNames.isEmpty()){
                for (String grantedRoleName:grantedRoleNames){
                    if (allRoleNames.contains(grantedRoleName)){
                        RoleDto roleDto = roleObject.get(grantedRoleName, null);
                        roleDtos.add(roleDto);
                    }
                }
            }
            allNames.add(allSchemaNames);
            allNames.add(allTableNames);
            allNames.add(allColumnNames);
            userDto =UserDto.builder()
                    .name(name)
                    .sysPrivDto(sysPrivDto)
                    .databasePrivDtos(databasePrivDtos)
                    .tablePrivDtos(tablePrivDtos)
                    .columnPrivDtos(columnPrivDtos)
                    .roleDtos(roleDtos)
                    .allNames(allNames)
                    .build();

        return userDto;
    }

    @Override
    public UserDto convert(UserDto userDto, Map<String, Object> params) {
        return null;
    }

    @Override
    public String sql(UserDto userDto, Map<String, Object> params) {
        return null;
    }

}
